dbms_metadata使用

oracle数据库提供了dbms_metadata API方便用户获取创建数据库对象的DDL语句，扩展出来的用途包括对比不同数据库中对象的差异。下面先介绍dbms_metadata的简单应用

获取数据库对象创建DDL

比较通用的方法

可以使用如下pl/sql函数获取对象的DDL语句

CREATE OR REPLACE FUNCTION get_table_md
  RETURN CLOB
IS
  -- Define local variables.
  h  NUMBER; --handle returned by OPEN
  th NUMBER; -- handle returned by ADD_TRANSFORM
  doc CLOB;
BEGIN
  -- Specify the object type.
  h := DBMS_METADATA.OPEN('TABLE');
  dbms_output.put_line('H is '||h );
  -- Use filters to specify the particular object desired.
  DBMS_METADATA.SET_FILTER(h,'SCHEMA','APPLSYS');
  DBMS_METADATA.SET_FILTER(h,'NAME','FND_USER');
  -- Request that the metadata be transformed into creation DDL.
  th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');
  -- Fetch the object.
  doc := DBMS_METADATA.FETCH_CLOB(h);
  -- Release resources.
  DBMS_METADATA.CLOSE(h);
  RETURN doc;
END;
/ 

数据库中创建get_table_md函数之后，可以用以下方式获取DDL语句：

SET PAGESIZE 0
SET LONG 1000000
SELECT get_table_md from dual;

比较方便的方法

在比较新的ORACLE版本中（我观察是11.1以上），可以直接使用dbms_metadata.get_ddl方法来获取DDL语句。

SET PAGESIZE 0
SET LONG 1000000
select dbms_metadata.get_ddl('TABLE','FND_NODES','APPLSYS') from dual;

可能碰到的问题

使用dbms_metadata.get_ddl时，可能会遇到类似下面的错误

*
ERROR at line 1:
ORA-31603: object "OBJECT1" of type TYPE not found in schema "USER1"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 628
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at "USER2.TEST", line 4
ORA-06512: at line 1

根据MOS文章DBMS_METADATA.GET_DDL Returns Error When Select Types Ora-31603 (文档 ID 312883.1)，此问题是因为用户缺少SELECT_CATALOG_ROLE角色权限导致，授予相应用户这个权限之后，问题即可解决。